%set up for database
host = 'localhost';
user = 'root';
password = '123456';
dbName = 'quant_data';
dbConn = database(dbName, user, password, ...
    'com.mysql.jdbc.Driver',sprintf('jdbc:mysql://localhost/%s',dbName));

fileID = fopen('log_database_data.txt','w');

%open file in folder
dirName = 'data\FI';
dirData = dir(dirName);      %# Get the data for the current directory
dirIndex = [dirData.isdir];  %# Find the index for directories
fileList = {dirData(~dirIndex).name}';  %'# Get a list of the files


% sql_template = ['INSERT INTO `financial_ratio` VALUES(0,%d,%d,%d'...
%     ',%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f'...
%     ',%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f'...
%     ',1)'];
sql_template = ['INSERT INTO `financial_ratio` VALUES(0,%d,%d,%d'...
    ',%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f'...
    ',%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f'...
    ',1)'];

fprintf(fileID,'--------START %s -----------\n',datestr(now));
    
for i = 1 : length(fileList)
    filename = fileList{i}(1:end-4); 
    attr = regexp(filename, '[_.]', 'split');
    pathFile = [dirName '\' fileList{i}];
    [ndata, text, alldata] = xlsread(pathFile);
    ticker = attr{2};
    type = attr{1};
    security_id = get_info_from_database('sec_id', ticker);
    if(~isnumeric(security_id))
        fprintf('\n    ... Can not find security with ticker %s\n',ticker);
        fprintf(fileID,'\n    ... Can not find security with ticker %s\n',ticker);
        continue
    end
    
    fprintf('\n\n----------Importing %d/%d , %s (%d) ...... ',i,length(fileList),ticker,security_id);
    fprintf(fileID,'\n\n----------Importing %d/%d , %s (%d) ...... ',i,length(fileList),ticker,security_id);
    
    if(type~='0')
        fprintf('\n    ... Do NOT Insert record due to different format\n');    
        fprintf(fileID,'\n    ... Do NOT Insert record %s due to different format\n',ticker); 
        continue;
    end
    
    
    year = 2012;
    quarter = 4;     
    m = 2;
    while(~isempty(text{3,m}))
        m = m + 1;
        quarter = quarter-1;
        if(quarter == 0 )
            quarter = 4;
            year = year - 1;
        end
    end
    
%     if(~isnumeric(alldata{3,3}))
%         quarter = 3;
%     end
%     if(mod(size(ndata,2),4)==2)
%         quarter = 2;
%     end
    processed_data = ndata;
%     processed_data([6 13 15 19 29 36 43 49],:) = [];  
    processed_data([6 13 15 19 27 34 41 47],:) = [];  %new data (2 cot EBITDA bo)
    
    l2 = size(processed_data,2);
    
    for j = 1: l2
       if(quarter==0) 
           year = year - 1;
           quarter = 4;
       end
       
       temp = sprintf(sql_template,[year quarter security_id processed_data(:,j)']);
       sql = strrep(temp, ',NaN', ',null');
       x = exec(dbConn, sql);
       fprintf(fileID,'\n    RESULT:... %s ',x.Message);    
       quarter = quarter - 1;
       
    end
end

fprintf(fileID,'--------END %s -----------\n',datestr(now));
fclose(fileID);